package com.css.zfzx.data.primary.yhDisasterPG.repository.impl;

import com.css.zfzx.data.primary.yhDisasterPG.repository.YhDisasterPGNativeRepository;
import com.css.zfzx.data.primary.yhDisasterPG.repository.entity.YhDisasterPGEntity;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.time.LocalDateTime;
import java.util.List;

/**
 * @author lxg
 * @date 2020-11-10
 */
@Repository
@PropertySource("classpath:platform-config.yml")
public class YhDisasterPGNativeRepositoryImpl implements YhDisasterPGNativeRepository {
    @PersistenceContext
    private EntityManager em;

    private static final Logger log = LoggerFactory.getLogger(YhDisasterPGNativeRepositoryImpl.class);

    @Override
    public List<String> findAreaCreate(LocalDateTime createTime,String menu) {
        StringBuilder sql = new StringBuilder("select area from yh_disaster ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (createTime!=null){
            whereSql.append(" and create_time >:createTime");
        }else {
            whereSql.append(" and create_time is not null");
        }
        if (StringUtils.isNotEmpty(menu)&&!"null".equals(menu)){
            whereSql.append(" and yh_project_type =:menu");
        }
        whereSql.append(" and is_valid = 1");
        whereSql.append(" and update_time is null");
        whereSql.append(" and evaluation_result is not null and evaluation_result !='未评估'");
        whereSql.append(" group by province,city,area,yh_project_type");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString());
        if (createTime!=null) {
            query.setParameter("createTime", createTime);
        }
        if (StringUtils.isNotEmpty(menu)&&!"null".equals(menu)){
            query.setParameter("menu", menu);
        }
        List<String> list = query.getResultList();
        return list;
    }

    @Override
    public List<YhDisasterPGEntity> findYhDisasterFwPGCreate(LocalDateTime createTime,String area,String menu) {
        StringBuilder sql = new StringBuilder("select * from yh_disaster ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (createTime!=null){
            whereSql.append(" and create_time >:createTime");
        }else {
            whereSql.append(" and create_time is not null");
        }
        if (StringUtils.isNotEmpty(menu)&&!"null".equals(menu)){
            whereSql.append(" and yh_project_type =:menu");
        }
        if (StringUtils.isNotEmpty(area)&&!"null".equals(area)){
            whereSql.append(" and area =:area");
        }
        whereSql.append(" and is_valid = 1");
        whereSql.append(" and update_time is null");
        whereSql.append(" and evaluation_result is not null and evaluation_result !='未评估'");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), YhDisasterPGEntity.class);
        if (createTime!=null) {
            query.setParameter("createTime", createTime);
        }
        if (StringUtils.isNotEmpty(menu)&&!"null".equals(menu)){
            query.setParameter("menu", menu);
        }
        if (StringUtils.isNotEmpty(area)&&!"null".equals(area)){
            query.setParameter("area", area);
        }
        List<YhDisasterPGEntity> list = query.getResultList();
        return list;
    }

    @Override
    public List<YhDisasterPGEntity> findYhDisasterPGCreate(LocalDateTime createTime,int identify) {
        StringBuilder sql = new StringBuilder("select * from yh_disaster ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (createTime!=null){
            whereSql.append(" and create_time >:createTime");
        }else {
            whereSql.append(" and create_time is not null");
        }
        whereSql.append(" and is_valid = 1");
        if (identify==1) {
            whereSql.append(" and yh_project_type in ('住宅','大中小学校舍','医疗卫生设施', '社会服务保障设施', '商业中心','其他')");
            //whereSql.append(" and yh_project_type in ('老旧居民小区','学校','医院', '商业中心', '养老福利院')");
        }else if (identify==2){
            whereSql.append(" and yh_project_type like '%桥梁%'");
        }else if(identify==3){
            whereSql.append(" and yh_project_type like '%水%'");
        }
        whereSql.append(" and update_time is null");
        whereSql.append(" and evaluation_result is not null and evaluation_result !='未评估'");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), YhDisasterPGEntity.class);
        if (createTime!=null) {
            query.setParameter("createTime", createTime);
        }
        List<YhDisasterPGEntity> list = query.getResultList();
        return list;
    }

    @Override
    public List<String> findAreaUpdate(LocalDateTime updateTime,String menu) {
        StringBuilder sql = new StringBuilder("select area from yh_disaster ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (updateTime!=null) {
            whereSql.append(" and update_time >:updateTime");
        }else {
            whereSql.append(" and update_time is not null");
            whereSql.append(" and is_valid=1");
        }
        if (StringUtils.isNotEmpty(menu)&&!"null".equals(menu)){
            whereSql.append(" and yh_project_type =:menu");
        }
        //whereSql.append(" and is_valid = 1");
        whereSql.append(" and evaluation_result is not null and evaluation_result !='未评估'");
        whereSql.append(" group by province,city,area,yh_project_type");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString());
        if (updateTime!=null) {
            query.setParameter("updateTime", updateTime);
        }
        if (StringUtils.isNotEmpty(menu)&&!"null".equals(menu)){
            query.setParameter("menu", menu);
        }
        List<String> list = query.getResultList();
        return list;
    }

    @Override
    public List<YhDisasterPGEntity> findYhDisasterFwPGUpdate(LocalDateTime updateTime, String area,String menu) {
        StringBuilder sql = new StringBuilder("select * from yh_disaster ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (updateTime!=null){
            whereSql.append(" and update_time >:updateTime");
        }else {
            whereSql.append(" and update_time is not null");
            whereSql.append(" and is_valid=1");
        }
        if (StringUtils.isNotEmpty(menu)&&!"null".equals(menu)){
            whereSql.append(" and yh_project_type =:menu");
        }
        if (StringUtils.isNotEmpty(area)&&!"null".equals(area)){
            whereSql.append(" and area =:area");
        }
        //whereSql.append(" and is_valid = 1");
        whereSql.append(" and evaluation_result is not null and evaluation_result !='未评估'");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), YhDisasterPGEntity.class);
        if (updateTime!=null) {
            query.setParameter("updateTime", updateTime);
        }
        if (StringUtils.isNotEmpty(menu)&&!"null".equals(menu)){
            query.setParameter("menu", menu);
        }
        if (StringUtils.isNotEmpty(area)&&!"null".equals(area)){
            query.setParameter("area", area);
        }
        List<YhDisasterPGEntity> list = query.getResultList();
        return list;
    }

    @Override
    public List<YhDisasterPGEntity> findYhDisasterPGUpdate(LocalDateTime updateTime,int identify) {
        StringBuilder sql = new StringBuilder("select * from yh_disaster ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (updateTime!=null) {
            whereSql.append(" and update_time >:updateTime");
        }else {
            whereSql.append(" and update_time is not null");
            whereSql.append(" and is_valid=1");
        }
        if (identify==1) {
            whereSql.append(" and yh_project_type in ('住宅','大中小学校舍','医疗卫生设施', '社会服务保障设施', '商业中心','其他')");
            //whereSql.append(" and yh_project_type in ('老旧居民小区','学校','医院', '商业中心', '养老福利院')");
        }else if (identify==2){
            whereSql.append(" and yh_project_type like '%桥梁%'");
        }else if(identify==3){
            whereSql.append(" and yh_project_type like '%水%'");
        }
        whereSql.append(" and evaluation_result is not null and evaluation_result !='未评估'");
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), YhDisasterPGEntity.class);
        if (updateTime!=null) {
            query.setParameter("updateTime", updateTime);
        }
        List<YhDisasterPGEntity> list = query.getResultList();
        return list;
    }
}
